package net.hn.hnms.biz.training.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.collect.Maps;
import com.google.common.net.MediaType;
import net.hn.hnms.biz.training.domain.AssessmentResultEntity;
import net.hn.hnms.biz.training.mapper.AssessmentResultMapper;
import net.hn.hnms.biz.training.request.AccessmentResultReq;
import net.hn.hnms.biz.training.request.AccessmentStatisticsReq;
import net.hn.hnms.biz.training.service.IAssessmentResultService;
import net.hn.hnms.biz.training.vo.AccessmentResultVO;
import net.hn.hnms.biz.training.vo.AccessmentScoreVO;
import net.hn.hnms.sys.common.core.constant.Constants;
import net.hn.hnms.sys.common.core.domain.ResponsePage;
import net.hn.hnms.sys.common.core.exception.HuaNengException;
import net.hn.hnms.sys.common.excel.core.ExcelResult;
import net.hn.hnms.sys.common.excel.utils.ExcelUtil;
import net.hn.hnms.sys.common.mybatis.core.page.PageQuery;
import net.hn.hnms.sys.common.redis.utils.RedisUtils;
import org.apache.commons.collections4.MapUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.Collator;
import java.time.Duration;
import java.util.*;

/**
 * @author wangjingrui
 * @date 2022/8/24 14:06
 */
@Service
public class AssessmentResultServiceImpl implements IAssessmentResultService {

    private final static String cacheKey = "assessment-result-dict";

    @Autowired
    private AssessmentResultMapper assessmentResultMapper;

    /**
     * @Description 导入考评结果
     * @Date 14:24 2022/8/24
     * @Param  date 考评时间; file导入的excel文件
     * @return 无
     **/
    @Override
    public void importResult(Date date, MultipartFile file,String mineCode) {
        String sufix = StrUtil.subAfter(file.getOriginalFilename(), ".", true);
        if(!"xls".equals(sufix) && !"xlsx".equals(sufix)) throw new HuaNengException(Constants.FAIL, "文件格式仅支持xls或xlsx");

        try {
            ExcelResult<AssessmentResultEntity> excel = ExcelUtil.importExcel(file.getInputStream(), AssessmentResultEntity.class, 2);
            List<AssessmentResultEntity> entitiesFromExcel = excel.getList();
            if(CollUtil.isEmpty(entitiesFromExcel)){
                if(CollUtil.isEmpty(excel.getErrorList())){
                    throw new HuaNengException(Constants.FAIL, "文件未包含有效数据");
                }
                throw new HuaNengException(Constants.FAIL, excel.getErrorList().get(0));
            }
            entitiesFromExcel.stream().distinct().forEach(item -> item.setExaminationDate(date));
            entitiesFromExcel.stream().distinct().forEach(item -> item.setMineCode(mineCode));

            int batchSize = 1000;
            int count = (int) Math.ceil(entitiesFromExcel.size() / (double) batchSize);
            for (int i = 0; i < count; i++) {
                assessmentResultMapper.insertBatch(CollUtil.sub(entitiesFromExcel, i * batchSize, (i + 1) * batchSize));
            }
            RedisUtils.deleteObject(cacheKey);
        }catch (IOException e){
            throw new HuaNengException(Constants.FAIL, "读取文件异常");
        }
    }

    /**
     * @Description 考评结果分页列表
     * @Date 14:25 2022/8/24
     * @Param  req 查询参数; pageQuery 分页参数
     * @return AccessResultVO 分页结果
     **/
    @Override
    public ResponsePage<AccessmentResultVO> selectPageList(AccessmentResultReq req, PageQuery pageQuery) {
        IPage<AccessmentResultVO> result = assessmentResultMapper.selectVoPage(pageQuery.build(), buildQuery(req));
        return new ResponsePage(result.getRecords(), (int) result.getTotal());
    }


    private LambdaQueryWrapper<AssessmentResultEntity> buildQuery(AccessmentResultReq req){
        LambdaQueryWrapper<AssessmentResultEntity> query = Wrappers.lambdaQuery();
        query.eq(StrUtil.isNotBlank(req.getDepartment()),AssessmentResultEntity::getDepartment,req.getDepartment());
        query.eq(StrUtil.isNotBlank(req.getPost()),AssessmentResultEntity::getPost,req.getPost());
        query.eq(StrUtil.isNotBlank(req.getExaminationResult()),AssessmentResultEntity::getExaminationResult,req.getExaminationResult());
        query.eq(StrUtil.isNotBlank(req.getMineCode()), AssessmentResultEntity::getMineCode, req.getMineCode());
        query.like(StrUtil.isNotBlank(req.getPersonName()),AssessmentResultEntity::getPersonName,req.getPersonName());
        query.like(StrUtil.isNotBlank(req.getIdentityCode()),AssessmentResultEntity::getIdentityCode,req.getIdentityCode());
        query.orderByDesc(AssessmentResultEntity::getExaminationDate);
        return query;
    }

    /**
     * 获取下拉菜单的值
     * @author Wang Jingrui
     * @date 2022/8/24 16:01
     * @Param  无
     * @return Map<String, Set<String>>
     */
    @Override
    public Map<String, Set<String>> dict(String mineCode) {
        Map<String, Set<String>> result = null;
        if(RedisUtils.hasKey(cacheKey)){
            //小于1分钟 重置缓存时间
            result = RedisUtils.getCacheObject(cacheKey);
            if(RedisUtils.getTimeToLive(cacheKey) < 60000){
                RedisUtils.expire(cacheKey, Duration.ofMinutes(10));
            }
            return result;
        }
        QueryWrapper<AssessmentResultEntity> wrapper = new QueryWrapper<>();
        wrapper.eq(StrUtil.isNotBlank(mineCode), "mine_code", mineCode);
        wrapper.groupBy("post","department","examination_result");
        wrapper.select("post", "department", "examination_result");
        List<AssessmentResultEntity> entities = assessmentResultMapper.selectList(wrapper);

        result = new HashMap<>();
        Set<String> postSet = new HashSet<>();
        Set<String> departmentSet = new HashSet<>();
        Set<String> examinationResultSet = new HashSet<>();
        result.put("post",postSet);
        result.put("department",departmentSet);
        result.put("examinationResult",examinationResultSet);
        entities.forEach(item -> {
            postSet.add(item.getPost());
            departmentSet.add(item.getDepartment());
            examinationResultSet.add(item.getExaminationResult());
        });

        RedisUtils.setCacheObject(cacheKey,result, Duration.ofMinutes(10));
        return result;
    }

    /**
     * 考评分段人数占比饼图
     */
    @Override
    public List<Map<String, Object>> pieChart(AccessmentStatisticsReq req) {
        List<Map<String, Object>> result = new ArrayList<>();

        QueryWrapper<AssessmentResultEntity> wrapper = new QueryWrapper<>();
        wrapper.select("sum(case when achievement > 90 and achievement <= 100 then 1 else 0 end) AS \"90-100分\"",
                "sum(case when achievement > 80 and achievement <= 90 then 1 else 0 end) AS \"80-90分\"",
                "sum(case when achievement > 70 and achievement <= 80 then 1 else 0 end) AS \"70-80分\"",
                "sum(case when achievement > 60 and achievement <= 70 then 1 else 0 end) AS \"60-70分\"",
                "sum(case when achievement <= 60 then 1 else 0 end) AS \"60-0分\"");
        setCondition(wrapper,req);

        List<Map<String, Object>> sumList = assessmentResultMapper.selectMaps(wrapper);
        if(CollUtil.isEmpty(sumList) || sumList.get(0) == null) return null;

        sumList.stream().flatMap(item -> item.entrySet().stream()).forEach(entry -> {
            Map<String, Object> map = Maps.newHashMap();
            map.put("name", entry.getKey());
            map.put("value", entry.getValue());
            map.entrySet().stream().sorted(Map.Entry.comparingByKey()).forEachOrdered(e -> map.put(e.getKey(), e.getValue()));
            result.add(map);
        });
        Collections.sort(result, new Comparator<Map<String, Object>>() {
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                String name1= MapUtils.getString(o1, "name");
                String name2=MapUtils.getString(o2, "name");
                Collator instance = Collator.getInstance(Locale.CHINA);
                return instance.compare(name1, name2);

            }
        });
        return result;
    }


    private void setCondition(QueryWrapper<AssessmentResultEntity> wrapper, AccessmentStatisticsReq req){
        wrapper.eq(StrUtil.isNotBlank(req.getDepartment()),"department",req.getDepartment());
        wrapper.eq(StrUtil.isNotBlank(req.getPost()),"post",req.getPost());
        wrapper.eq(StrUtil.isNotBlank(req.getMineCode()), "mine_code", req.getMineCode());
        if(req.getBeginDate() != null) wrapper.apply(" examination_date >= to_date('"+DateUtil.formatDate(req.getBeginDate())+"','YYYY-MM') ");
        if(req.getEndDate() != null) wrapper.apply(" examination_date <= to_date('"+DateUtil.formatDate(req.getEndDate())+"','YYYY-MM') ");
    }

    /**
     * 考评分段人数统计折线图
     */
    @Override
    public List<AccessmentScoreVO> lineChart(AccessmentStatisticsReq req) {
        List<AccessmentScoreVO> result = new ArrayList<>();

        boolean crossYear = req.getBeginDate() != null && req.getBeginDate().getYear() != Calendar.getInstance().get(Calendar.YEAR);
        String formatStr = crossYear ? "yyyy-MM" : "MM";

        QueryWrapper<AssessmentResultEntity> wrapper = new QueryWrapper<>();
        wrapper.select("sum(case when achievement >= 80 then 1 else 0 end) AS pass_num",
                       "count(examination_id) AS all_num",
                       "to_char(examination_date,'" + formatStr +"') as date");
        wrapper.groupBy("to_char(examination_date,'" + formatStr +"')");
        setCondition(wrapper,req);
        List<Map<String, Object>> sumList = assessmentResultMapper.selectMaps(wrapper);

        List<DateTime> dateTimes = null;
        if(req.getBeginDate() != null){
            dateTimes = DateUtil.rangeToList(req.getBeginDate(), req.getEndDate(), DateField.MONTH);
        } else {
            Date begin = net.hn.hnms.biz.training.utils.DateUtil.getCurrYearFirst();
            Date end = new DateTime();
            dateTimes = DateUtil.rangeToList(begin, end, DateField.MONTH);
        }
        dateTimes.forEach(item -> {
            Map<String, Object> voMap = sumList.stream().filter(sumItem -> DateUtil.format(item, formatStr).equals(sumItem.get("date"))).findFirst().orElse(null);
            if(null != voMap) {
                result.add(BeanUtil.copyProperties(voMap, AccessmentScoreVO.class));
            } else {
                result.add(AccessmentScoreVO.builder().allNum(0L).passNum(0L).date(DateUtil.format(item,formatStr)).build());
            }
        });
        return result;
    }

    /**
     * 考评分段人数统计柱状图
     */
    @Override
    public List<List<Object>> columnChart(AccessmentStatisticsReq req) {
        QueryWrapper<AssessmentResultEntity> wrapper = new QueryWrapper<>();
        wrapper.eq(StrUtil.isNotBlank(req.getMineCode()), "mine_code", req.getMineCode());

        if(req.getBeginDate() != null) wrapper.apply(" examination_date >= to_date('"+DateUtil.formatDate(req.getBeginDate())+"','YYYY-MM') ");
        if(req.getEndDate() != null) wrapper.apply(" examination_date <= to_date('"+DateUtil.formatDate(req.getEndDate())+"','YYYY-MM') ");
        wrapper.groupBy(StrUtil.isBlank(req.getType()) ? "post" : req.getType());
        wrapper.select("sum(case when achievement > 90 and achievement <= 100 then 1 else 0 end) AS gt90",
                "sum(case when achievement > 80 and achievement <= 90 then 1 else 0 end) AS gt80",
                "sum(case when achievement > 70 and achievement <= 80 then 1 else 0 end) AS gt70",
                "sum(case when achievement > 60 and achievement <= 70 then 1 else 0 end) AS gt60",
                "sum(case when achievement <= 60 then 1 else 0 end) AS lt60",
                "post".equals(StrUtil.isBlank(req.getType()) ? "post" : req.getType()) ? "post as post_or_department": "department as post_or_department");

        List<Map<String, Object>> sumList = assessmentResultMapper.selectMaps(wrapper);
        if(CollUtil.isEmpty(sumList) || sumList.get(0) == null) return null;

        List<List<Object>> result = new LinkedList<>();
        sumList.forEach(item -> {
            List<Object> vo = new LinkedList();
            vo.add(item.get("post_or_department"));
            vo.add(item.get("gt90"));
            vo.add(item.get("gt80"));
            vo.add(item.get("gt70"));
            vo.add(item.get("gt60"));
            vo.add(item.get("lt60"));
            result.add(vo);
        });
        return result;
    }

    /**
     * 考评结果导入
     */
    @Override
    public void downloadTemplate(HttpServletResponse response) {
        OutputStream fos = null;
        ExcelWriter writer = null;
        try {
            fos = response.getOutputStream();
            writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);

            List<String> header = CollUtil.newArrayList("序号", "区科", "姓名", "身份证号", "岗位工种", "理论成绩", "考试结果");
            List<List<String>> rows = new ArrayList<>();
            rows.add(header);
            writer.merge(6, "考试结果");
            writer.write(rows, true);

            response.reset();
            response.setContentType(MediaType.OCTET_STREAM.toString());
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode("培训考评-考试结果-模板.xlsx", "UTF-8"));
            writer.flush(fos, true);
        } catch (IOException e) {
            throw new HuaNengException(Constants.FAIL, "模板下载失败");
        } finally {
            IoUtil.close(fos);
            if (writer != null) writer.close();
        }
    }
}
